import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_职业前景满意度(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None,
    group_by_field: Optional[str] = None
) -> Dict[str, Any]:
    """
    职业前景满意度 - 指标计算函数
    
    ## 指标说明
    该指标用于评估毕业生对职业前景的满意度，计算逻辑为：问卷中特定问题（T00000637）的前三项选项占比之和相对于前五项选项占比之和的比率。
    可以根据需求按学生属性（如院系、专业、性别等）进行分组统计，也可以按学历进行筛选。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        education (Optional[str]): 学历筛选条件，可选值：[本科毕业生，专科毕业生，硕士研究生，博士研究生]
        group_by_field (Optional[str]): 分组维度字段，可选值：['college', 'spec', 'sex']
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "education": "本科毕业生",
        "group_by_field": "college"
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": {
            "summary": {
                "satisfaction_ratio": 0.7150,
                "total_count": 200,
                "satisfied_count": 143,
                "description": "职业前景满意度为 71.50%"
            },
            "distribution": [
                {
                    "dimension_name": "计算机学院",
                    "dimension_field": "college",
                    "ratio": 0.75,
                    "count": 120
                },
                {
                    "dimension_name": "经济学院",
                    "dimension_field": "college",
                    "ratio": 0.68,
                    "count": 80
                }
            ]
        }
    }
    ```
    """
    logger.info(f"开始计算指标: 职业前景满意度, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 参数验证
        allowed_fields = ['college', 'spec', 'sex']
        if group_by_field and group_by_field not in allowed_fields:
            raise ValueError(f"无效的分组字段: {group_by_field}, 只允许: {allowed_fields}")

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year, dy_target_items, split_tb_paper 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        split_tb_paper = project_info['split_tb_paper']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}, split_tb_paper={split_tb_paper}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询问卷信息
        questionnaire_sql = f"""
        SELECT id, dy_target 
        FROM wt_template_customer 
        WHERE id IN ({','.join(['%s'] * len(questionnaire_ids))})
        """
        questionnaires = db.fetchall(questionnaire_sql, tuple(questionnaire_ids))
        if not questionnaires:
            raise ValueError(f"未找到问卷ID集合={questionnaire_ids}的配置信息")
        
        logger.info(f"查询到问卷信息: {questionnaires}")

        # 4. 过滤特定调研对象的问卷
        valid_questionnaire_ids = [q['id'] for q in questionnaires if q['dy_target'] == 'GRADUATE_SHORT']
        if not valid_questionnaire_ids:
            raise ValueError("未找到目标调研对象(GRADUATE_SHORT)的问卷ID")
            
        logger.info(f"找到有效问卷ID: {valid_questionnaire_ids}")

        # 5. 查询问题信息
        question_sql = """
        SELECT id, wt_code, wt_obj 
        FROM wt_template_question_customer 
        WHERE cd_template_id = %s AND wt_code = %s AND is_del = 0
        """
        question_info = db.fetchone(question_sql, (valid_questionnaire_ids[0], 'T00000637'))
        if not question_info:
            raise ValueError("未找到指定问题编码(T00000637)的问题信息")
            
        logger.info(f"找到问题信息: {question_info['id']}")

        # 6. 构建动态表名
        answer_table = f"re_dy_paper_answer_{split_tb_paper}"
        student_table = f"dim_client_target_baseinfo_student_{item_year}"

        # 7. 构建SQL查询条件
        where_conditions = [
            f"t1.cd_template_id = {valid_questionnaire_ids[0]}",
            f"t1.wid = {question_info['id']}",
            "t1.ans_true = 1",
            f"s.shard_tb_key = '{shard_tb_key}'",
            f"s.item_year = '{item_year}'"
        ]
        
        if education:
            where_conditions.append(f"s.education = '{education}'")
        
        if group_by_field:
            where_conditions.append(f"s.{group_by_field} != ''")
            group_by_clause = f"GROUP BY s.{group_by_field}"
            select_fields = f"s.{group_by_field} AS group_field"
        else:
            group_by_clause = ""
            select_fields = "'总体' AS group_field"

        # 8. 执行计算SQL
        calc_sql = f"""
        SELECT
            {select_fields},
            (SUM(c1)+SUM(c2)+SUM(c3))/(SUM(c1)+SUM(c2)+SUM(c3)+SUM(c4)+SUM(c5)) as ratio
        FROM
            {answer_table} t1
            JOIN {student_table} s ON t1.target_no = s.target_no
        WHERE
            {' AND '.join(where_conditions)}
        {group_by_clause}
        """
        
        logger.info(f"执行计算SQL: {calc_sql}")
        results = db.fetchall(calc_sql)
        if not results:
            raise ValueError("未找到有效的计算数据")

        # 9. 处理结果
        if group_by_field:
            # 有分组的情况
            distribution = []
            total_count = 0
            satisfied_count = 0
            
            for row in results:
                ratio = float(row['ratio']) if row['ratio'] is not None else 0.0
                distribution.append({
                    "dimension_name": row['group_field'],
                    "dimension_field": group_by_field,
                    "ratio": round(ratio, 4),
                    "count": 0  # 这里需要单独查询获取count
                })
            
            # 计算整体数据
            overall_sql = f"""
            SELECT
                (SUM(c1)+SUM(c2)+SUM(c3)) as satisfied_count,
                (SUM(c1)+SUM(c2)+SUM(c3)+SUM(c4)+SUM(c5)) as total_count
            FROM
                {answer_table} t1
                JOIN {student_table} s ON t1.target_no = s.target_no
            WHERE
                {' AND '.join(where_conditions[:-1])}  # 排除分组条件
            """
            overall_result = db.fetchone(overall_sql)
            total_count = overall_result['total_count'] or 0
            satisfied_count = overall_result['satisfied_count'] or 0
            satisfaction_ratio = satisfied_count / total_count if total_count > 0 else 0
            
        else:
            # 无分组的情况
            distribution = []
            if results:
                row = results[0]
                ratio = float(row['ratio']) if row['ratio'] is not None else 0.0
                
                # 获取具体数量
                count_sql = f"""
                SELECT
                    (SUM(c1)+SUM(c2)+SUM(c3)) as satisfied_count,
                    (SUM(c1)+SUM(c2)+SUM(c3)+SUM(c4)+SUM(c5)) as total_count
                FROM
                    {answer_table} t1
                    JOIN {student_table} s ON t1.target_no = s.target_no
                WHERE
                    {' AND '.join(where_conditions)}
                """
                count_result = db.fetchone(count_sql)
                total_count = count_result['total_count'] or 0
                satisfied_count = count_result['satisfied_count'] or 0
                satisfaction_ratio = ratio
            else:
                total_count = 0
                satisfied_count = 0
                satisfaction_ratio = 0

        logger.info(f"指标 '职业前景满意度' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": {
                "summary": {
                    "satisfaction_ratio": round(satisfaction_ratio, 4),
                    "total_count": total_count,
                    "satisfied_count": satisfied_count,
                    "description": f"职业前景满意度为 {satisfaction_ratio * 100:.2f}%"
                },
                "distribution": distribution
            }
        }

    except Exception as e:
        logger.error(f"计算指标 '职业前景满意度' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 职业前景满意度",
            "code": 500,
            "error": str(e)
        }